Access 2010 Case Project Assignment

100 points, due April 16^th

This project is designed to reinforce your knowledge of the database objects learned in the Access 2010 lab assignments. Create a new database named Case Project in your H:/CPSC 103 folder that will hold information about your own friends and family members who now attend colleges or universities.

1. Create 3 tables, each of which has a minimum of 25 records: The first table, named Students, contains a Student ID (text, 3 characters), Last Name, First Name, Street, City, State, Zip code, and Telephone. Include your own information as the last record of the Students table.

The second table, named College or University, contains the

Student ID (text, 3 characters), College Name, Major, Tuition (currency), GPA (number, with field properties of single, standard, 2), and Email Address.

The third table, named Relationships, contains the Student ID (text, 3 characters), your Relationship to the student (sibling, high school friend, childhood friend, college friend, cousin, etc.), Birthday Month (text i.e. January, April, etc.), Birth Date (date/time).

Print a copy of each of the 3 tables in landscape.

Define One-to-One relationships between the Students table and College or University table, and between the Students table and Relationships table. Enforce referential integrity in these relationships, Save. Create and Print the table relationships report. Include a label with your name in the relationships report header.

2. Create a form for each of the tables, using Students Form, Relationships Form, etc. as the form title and your own name as the form subtitle. Center and bold the form title. Change the position of at least 1 field name on each form (i.e. position First Name and Last Name on the same line). Include a subform with the ID, First and Last Name fields from the Students table as part of your Relationships Form. Place a command button with text saying Print Record on the top right Detail section of the Relationships form. Print one copy of your record only for each of the 3 forms.

3. Create 3 queries from the tables in step 1: The first query, named Birthday Query, contains Student ID, First Name, Last Name, College Name, Relationship, Birthday Month, and Birth Date. Sort this query on Last and First Names, Landscape.

The second query, named College Data Query, contains First Name, Last

Name, Email Address, College Name, Major, Relationship, and Tuition. Include a calculated field called Proposed New Tuition, which represents a 3% tuition increase. Sort this query by College Name, Landscape.

The third query, named Moderately Priced Colleges Query, contains First Name, Last Name, College Name, GPA, and Tuition. Display only those records where the tuition amount is between 15000 and 35000. Sort the query by the tuition amount in descending order, Portrait.

Print a copy of each query.

4. Create a set of mailing labels, named Student Address Labels, which use all the fields from the Students table except the Student ID and the Telephone number. Use the Avery C2160 template. Place the First and Last Names on the first line of the label, street address on the second line, and the City, State and Zip code together on the third line of the label. The labels should be sorted by Zip code. Print the labels.

5. Create 2 reports based on your queries from step 3: The first report, named Student Birthday Report with your name centered as subtitle, contains all fields from the Birthday Query except the Student ID. Sort the report on the Last and First Names. Group the report by Relationship and keep the groups together in the report. Format nicely, Portrait.

The second report, named College or University Data Report with your name centered as subtitle, contains all the fields from the College Data Query except Proposed New Tuition and Email Address. Group this report by College Name and by Major, keeping the groups together on the Major field. Sort by Last and First Names. Total the Tuition field for each Major group and display the percentage of total. You will probably have to drag the Tuition field into the Detail section of your report after completing the wizard.Format nicely, Landscape.

Print a copy of each report.

6. Create macros that will open and maximize each of your 3 forms, your labels and both of your reports. Include your name as the first comment in each macro.

Print the instructions for each macro.

Next, create 3 switchboard forms. The Main switchboard (default, named after you) should also include your name as a label in the header section of the form itself and have a graphic image on the left header. The Main switchboard page needs 2 items for Forms and Reports and a third item to Exit the application.

The Forms switchboard page should have 3 items that run the correct macro and a fourth item to return to the main switchboard.

The Reports switchboard page should have 3 items that run the correct label or report macro and a fourth item to return to the main switchboard.

Print your Switchboard Items table and a copy of each of the 3 switchboard forms.

7. Create a web page based on your Student Birthday Report, which could be viewed by prospective students browsing the Internet who might want to learn more about your Case Project database. Click the report name in the Navigation Pane and use the External Data Tab, Export Group, More drop down arrow, choosing HTML document. Browse to your CPDC 103 folder so the report is saved there. Place checkmarks in the first 2 boxes of the dialog box that appears, OK. Click OK again to accept the default HTML template settings. The Internet Explorer browser will now open with your report displayed. At the bottom of the document, you can choose next, last, first to see all pages of the report. This is not interactive, but can be viewed. Click the Printer icon, Print, OK. Print the report in Portrait from the browser.

Hand in all your printouts from steps 1-7.